﻿using Microsoft.VisualStudio.TestTools.UnitTesting;
using MySql.Data.MySqlClient;
using System;
using System.Data.SqlClient;

namespace CSUnitTest
{
    [TestClass]
    public class database
    {
        public static readonly log4net.ILog Logger = log4net.LogManager.GetLogger("myLog");

        //连接
        [TestMethod]
        public void TestMethod1()
        {
            //string connectionString = "Server=localhost;Database=test;User Id=root;pwd=root;";
            string connectionString = "server=localhost;database=test;uid=root;pwd=root";
            string sqlQuery = "SELECT * FROM t_user";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    Logger.Error("数据库连接失败");
                    return;
                }

                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                        while (reader.Read())
                        {
                            // 处理查询结果-不知道列名
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i);
                                object columnValue = reader.GetValue(i);
                                Console.WriteLine($"{columnName}: {columnValue}");
                            }

                            // 处理查询结果-知道列名
                            // 通过列索引获取值
                            int id = reader.GetInt32(0);
                            string name = reader.GetString(1);
                            string pwd = reader.GetString(reader.GetOrdinal("pwd"));
                            //int age = reader.GetInt32(reader.GetOrdinal("age"));
                            //string gender = reader.GetString(reader.GetOrdinal("gender"));
                            // 处理查询结果
                            Console.WriteLine($"{id} - {name} - {pwd}");
                            Console.WriteLine("--------"); // 分隔行
                        }
                }
            }
        }

        [TestMethod]
        public void TestMethod2()
        {
            string connectionString = "Server=localhost;Database=test;User Id=root;pwd=root;";
            string sqlQuery = "SELECT * FROM t_user";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand(sqlQuery, connection))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader()) 
                    {
                        while (reader.Read())
                        {
                            for(int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i).ToString();
                                object columnValue = reader.GetValue(i);
                                Logger.Info($"{columnName}:{columnValue}");
                            }
                        }
                    }

                    int count = cmd.ExecuteNonQuery();
                    Object first = cmd.ExecuteScalar();
                    Logger.Info($"受影响的条数：{count}");
                    Logger.Info($"第一行第一列数据：{first}");
                }

            }

        }

        //连接MySQL数据库
        [TestMethod]
        public void TestMethod3()
        {
            //string connectionString = "Server=localhost;Database=test;User Id=root;pwd=root;";
            string connectionString = "server=localhost;port=3306;database=test;user=root;password=root;";
            string sqlQuery = "SELECT * FROM view_user";
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                if(connection.State != System.Data.ConnectionState.Open)
                {
                    Logger.Error("连接MySQL失败");
                    return;
                }

                using (MySqlCommand cmd = new MySqlCommand(sqlQuery,connection))
                {
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            for(int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i).ToString();
                                object columnValue = reader.GetValue(i);
                                Logger.Info($"{columnName}--{columnValue}");
                            }
                        }
                    }
                }

                string sql2 = "SELECT name FROM view_user";
                using (MySqlCommand cmd = new MySqlCommand(sql2, connection))
                {
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                string columnName = reader.GetName(i).ToString();
                                object columnValue = reader.GetValue(i);
                                Logger.Info($"{columnName}--{columnValue}");
                            }
                        }
                    }
                }
            }
        }

    }
}
